const mysql = require('mysql2/promise');
const fs = require('fs');
const path = require('path');
require('dotenv').config({ path: path.join(__dirname, '../.env.local') });

console.log('\n🔧 开始初始化题库检测系统...\n');

async function initQuestionReview() {
  let connection;

  try {
    // 读取SQL脚本
    const sqlPath = path.join(__dirname, '../database/question-review-table.sql');
    console.log('📖 读取初始化脚本...');
    const sqlScript = fs.readFileSync(sqlPath, 'utf8');
    console.log('✅ SQL脚本读取成功\n');

    // 连接数据库
    console.log('📡 连接到数据库...');
    console.log(`📋 数据库配置:`);
    console.log(`   主机: ${process.env.DB_HOST}`);
    console.log(`   端口: ${process.env.DB_PORT}`);
    console.log(`   用户: ${process.env.DB_USER}`);
    console.log(`   数据库: ${process.env.DB_NAME}\n`);

    connection = await mysql.createConnection({
      host: process.env.DB_HOST || 'localhost',
      port: parseInt(process.env.DB_PORT || '3306'),
      user: process.env.DB_USER || 'root',
      password: process.env.DB_PASSWORD || '',
      database: process.env.DB_NAME || 'math_academy',
      multipleStatements: true
    });

    console.log('✅ 数据库连接成功\n');

    // 执行SQL脚本
    console.log('⚙️  执行初始化脚本...');
    await connection.query(sqlScript);
    console.log('✅ 初始化脚本执行成功\n');

    // 验证表是否创建
    console.log('🔍 验证数据表...');
    const [tables] = await connection.query(`
      SELECT TABLE_NAME 
      FROM information_schema.TABLES 
      WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 'question_reviews'
    `, [process.env.DB_NAME || 'math_academy']);

    if (tables.length > 0) {
      console.log('✅ 表 question_reviews 创建成功\n');
    } else {
      console.log('❌ 表创建失败\n');
      process.exit(1);
    }

    // 验证视图是否创建
    const [views] = await connection.query(`
      SELECT TABLE_NAME 
      FROM information_schema.VIEWS 
      WHERE TABLE_SCHEMA = ? AND TABLE_NAME IN ('question_review_stats', 'question_review_by_topic')
    `, [process.env.DB_NAME || 'math_academy']);

    console.log(`✅ 创建了 ${views.length} 个视图:`);
    views.forEach(view => {
      console.log(`   - ${view.TABLE_NAME}`);
    });
    console.log('');

    // 显示成功信息
    console.log('═══════════════════════════════════════');
    console.log('🎉 题库检测系统初始化完成！');
    console.log('═══════════════════════════════════════');
    console.log('✅ 数据库表创建成功');
    console.log('✅ 视图创建成功');
    console.log('✅ 系统已就绪');
    console.log('═══════════════════════════════════════\n');
    console.log('💡 下一步:');
    console.log('1. 使用管理员账号登录系统');
    console.log('2. 访问 管理员面板 > 题库检测');
    console.log('3. 开始检测题目！\n');

  } catch (error) {
    console.error('\n❌ 初始化失败:', error.message);
    console.error(error);
    process.exit(1);
  } finally {
    if (connection) {
      await connection.end();
    }
  }
}

// 执行初始化
initQuestionReview();

